<?php

namespace app\admin\controller;

use app\common\controller\AdminBase;
use think\App;
use think\exception\ValidateException;
use think\facade\Db;
use think\facade\Log;
use util\Excel;
use think\cache\driver\Redis;

/**
 * Dispute控制器
 */
class Ordershopify extends AdminBase
{

    protected $model = null;

    protected $validate = null;

    protected $relationSearch = false;

    public function __construct(App $app)
    {
        parent::__construct($app);

        $this->model = new \app\admin\model\OrderShopify();

        $this->validate = \app\admin\validate\OrderShopify::class;
    }

    /**
     * 列表
     */
    public function index()
    {
        $redis = new Redis();
        $page = $this->request->get('page', 1);
        $newpage = $page-1;
        if($newpage == 0){
            $limit = "0,10";
        }else{
            $newpage = $newpage*10;
            $limit = $newpage.",10";
        }

        $filters = $this->request->get('filter', '{}');
        $filters = json_decode($filters, true);
        $where = "1=1";
        if (isset($filters['email']) && !empty($filters['email'])) {
            $where = $where .' AND a.email like "%'.$filters['email'].'%"';
        }
        if (isset($filters['title']) && !empty($filters['title'])) {
            $where = $where .' AND a.title like "%'.$filters['title'].'%"';
        }
        if (isset($filters['is_published']) && !empty($filters['is_published'])) {
            switch ($filters['is_published']) {
                case '1':
                    $filters['is_published'] = '1';
                    break;
                case '2':
                    $filters['is_published'] = '0';
                    break;
                default:
                    $filters['is_published'] = '0';
            }
            $where = $where .' AND a.is_published = '.$filters['is_published'];
        }
        if (isset($filters['country']) && !empty($filters['country'])) {
            $country_count = count($filters['country']);
            if($country_count == 1){
                $where = $where .' AND a.country like "%'.$filters['country'][0].'%"';
            }else{
                $country_where = '';
                foreach ($filters['country'] as $k=>$v) {
                    if($k == 0){
                        $country_where = ' AND (a.country like "%'.$v.'%"';
                    }else{
                        $country_where = $country_where . ' or a.country like "%'.$v.'%"';
                    }
                }
                $country_where = $country_where . ')';
                $where = $where . '' . $country_where;
            } 
        }
        if (isset($filters['site_id']) && !empty($filters['site_id'])) {
            $site = table('site')->where('site_number',$filters['site_id'])->find();
            if(!empty($site)){
                $where = $where .' AND a.site_id = '.$site['id'];
            }
        }
        
        if (isset($filters['phone']) && !empty($filters['phone'])) {
            $where = $where .' AND a.phone like "%'.$filters['phone'].'%"';
        }
        if (isset($filters['count']) && !empty($filters['count'])) {
            $where = $where .' AND a.count = '.$filters['count'];
        }
        if (isset($filters['created_at']) && !empty($filters['created_at'])) {
            $where = $where .' AND b.created_at = "'.$filters['created_at'].'"';
        }
        if (isset($filters['sku']) && !empty($filters['sku'])) {
            $where = $where .' AND c.sku like "%'.$filters['sku'].'%"';
        }
        if($where == "1=1"){
            $join = '';
            $field = 'a.id,a.customer_id,a.ycreated_at,a.email,a.title,a.country,a.site_id,a.phone,a.count,a.is_published';
        }else{
            if (isset($filters['sku']) && !empty($filters['sku'])) {
                $join = 'LEFT JOIN qu_order as b ON a.customer_id = b.customers_id
                LEFT JOIN qu_order_quantity as c ON b.id=c.order_id';
                $field = 'a.id,a.customer_id,a.ycreated_at,a.email,a.title,a.country,a.site_id,a.phone,a.count,a.is_published,
                b.created_at,c.sku';
                $where = $where . " AND c.id > 1";
            }else{
                $join = 'LEFT JOIN qu_order as b ON a.customer_id = b.customers_id';
                $field = 'a.id,a.customer_id,a.ycreated_at,a.email,a.title,a.country,a.site_id,a.phone,a.count,a.is_published,b.created_at';
            }
        }
        $sql = "SELECT 
            ".$field."
            FROM qu_customer as a
            ".$join."
            WHERE ".$where."
            ORDER BY a.ycreated_at DESC
            LIMIT ".$limit;
        $join_datas = Db::query($sql);
        if(!empty($join_datas)){
            foreach ($join_datas as $k=>$v) {
                $site = $redis->get('site_name'.$v['site_id']);
                if (empty($site)) {
                    $site = table('site')
                    ->where('id', $v['site_id'])
                    ->find();
                    $redis->set('site_name'.$v['site_id'], $site, 60*60);
                }
                if (!empty($site)) {
                    $join_datas[$k]['site_name'] = $site['site_name'];
                } else {
                    $join_datas[$k]['site_name'] = '';
                }
                $join_datas[$k]['is_published'] = $v['is_published'] == 1 ? '已订阅' : '未订阅';
                if(empty($v['count']) || $v['count'] == null){
                    $join_datas[$k]['count'] = 0;
                }
                $join_datas[$k]['ycreated_at'] = date('Y-m-d', strtotime($v['ycreated_at']));
            }
        }
        // halt($join_datas);
        $sql_count = "SELECT
            COUNT(*) as count
            FROM qu_customer as a
            ".$join."
            WHERE ".$where;
        $count_datas = Db::query($sql_count);
        $data = [
            'code' => 1,
            'msg' => '',
            'count' => $count_datas[0]['count'],
            'data' => $join_datas,
        ];

        return json($data);
    }

    /**
     * 列表
     */
    public function classification()
    {
        $redis = new Redis();
        $new_data = $redis->get('classification');
        if(empty($new_data)){
            $sql = "
                SELECT
                COUNT(*) as count,country
                FROM qu_customer
                where country is not NULL
                GROUP BY country
                ORDER BY count desc
                LIMIT 10
            ";
            $datas = Db::query($sql);

            $sql_site = "
                SELECT
                site_number,site_url
                FROM qu_site
                where status = 'normal'
            ";
            $datas_site = Db::query($sql_site);

            $classification = array();
            foreach ($datas as $k=>$v) {
                if($v['country'] == 'United States'){
                    $classification1 = $this->classification_country($v);
                    $classification[] = $classification1;
                }
                if($v['country'] == 'Canada'){
                    $classification1 = $this->classification_country($v);
                    $classification[] = $classification1;
                }
                if($v['country'] == 'Australia'){
                    $classification1 = $this->classification_country($v);
                    $classification[] = $classification1;
                }
                if($v['country'] == 'United Kingdom'){
                    $classification1 = $this->classification_country($v);
                    $classification[] = $classification1;
                }
                if($v['country'] == 'Germany'){
                    $classification1 = $this->classification_country($v);
                    $classification[] = $classification1;
                }
            }
            $new_data['country'] = $datas;
            $new_data['site'] = $datas_site;
            $new_data['classification'] = $classification;
            $redis->set('classification', $new_data, 3600*24);
            $data = [
                'code' => 1,
                'msg' => '',
                'data' => $new_data,
            ];
        }else{
            $data = [
                'code' => 1,
                'msg' => '缓存数据',
                'data' => $new_data,
            ];
        }
        return json($data);
    }

    /**
    * 方法详情
    * @param array params
    */
    public function classification_country($v)
    {
        $sql = "
            SELECT 
            COUNT(b.countnum) as count
            FROM qu_customer as a
            LEFT JOIN qu_order as b ON a.email = b.email
            WHERE b.financial_status = 'paid'
            AND a.country = '".$v['country']."'
        ";
        $count = Db::query($sql);
        $v['paid'] = $count[0]['count'];
        $v['paid_not'] = $v['count'] - $v['paid'];
        
        $timedate = strtotime(date('Y-m-d 00:00:00', time()));
        $sql2 = "
            SELECT 
            COUNT(b.countnum) as count
            FROM qu_customer as a
            LEFT JOIN qu_order as b ON a.email = b.email
            WHERE a.country = '".$v['country']."'
            AND b.created_at > '".$timedate."'
        ";
        $count_today = Db::query($sql2);

        $sql3 = "
            SELECT 
            COUNT(b.countnum) as count
            FROM qu_customer as a
            LEFT JOIN qu_order as b ON a.email = b.email
            WHERE b.financial_status = 'paid'
            AND a.country = '".$v['country']."'
            AND b.created_at > '".$timedate."'
        ";
        $count_todaypaid = Db::query($sql3);
        
        $v['count_today'] = $count_today[0]['count'];
        $v['count_todaypaid'] = $count_todaypaid[0]['count'];

        $sql4 = "
            SELECT 
            COUNT(*) as count
            FROM qu_customer
            WHERE email is not NULL
            AND country = '".$v['country']."'
        ";
        $count_email = Db::query($sql4);

        $sql5 = "
        SELECT 
        COUNT(*) as count
        FROM qu_customer
        WHERE phone is not NULL
        AND country = '".$v['country']."'
    ";
        $count_phone = Db::query($sql5);
        
        $v['count_email'] = $count_email[0]['count'];
        $v['count_phone'] = $count_phone[0]['count'];

        return $v;
    }
    
    /**
     * 添加
     */
    public function add()
    {
        $post = $this->request->post();
        try {
            $this->validate && validate($this->validate)->check($post);

            if ($post['flag'] && is_array($post['flag'])) {
                $post['flag'] = implode(',', $post['flag']);
            }
            if ($post['actdata'] && is_array($post['actdata'])) {
                $post['actdata'] = implode(',', $post['actdata']);
            }
            if ($post['city'] && is_array($post['city'])) {
                $post['city'] = implode(',', $post['city']);
            }
            $result = $this->model->save($post);
            if ($result) {
                return success('添加成功！');
            }
            return error('添加失败');
        } catch (ValidateException $e) {

            return error($e->getError());
        } catch (\Exception $e) {
            return error('添加失败:' . $e->getMessage());
        }
    }

    /**
     * 查找
     */
    public function find()
    {
        $id = $this->request->get('id');
        $row = $this->model->find($id);
        if ($row['flag']) {
            $arr = explode(",", $row['flag']);
            foreach ($arr as $k => $v) {
                $arr[$k] = $v * 1;
            }
            $row['flag'] = $arr;
        } else {
            $row['flag'] = [];
        }
        if ($row['actdata']) {

            $arr = explode(",", $row['actdata']);
            foreach ($arr as $k => $v) {
                $arr[$k] = $v * 1;
            }
            $row['actdata'] = $arr;
        } else {
            $row['actdata'] = [];
        }
        if ($row['city']) {
            $row['city'] = explode(',', $row['city']);
        } else {
            $row['city'] = [];
        }
        if (empty($row)) {
            return error('数据不存在');
        }
        return success('ok', $row);
    }
    /**
     * 修改
     *
     */
    public function edit()
    {
        $id = input('id');
        $row = $this->model->find($id);
        if (empty($row)) {
            return error('数据不存在');
        }

        if ($this->request->isPost()) {
            $post = $this->request->post();
            try {
                $this->validate && validate($this->validate)->check($post);
                $result = $row->save($post);
                if ($result) {
                    return success('保存成功！');
                }
                return error('保存失败');
            } catch (ValidateException $e) {

                return error($e->getError());
            } catch (\Exception $e) {
                Log::error("--------:" . $e);
                return error('保存失败');
            }
        }
        if ($row['flag']) {
            $arr = explode(",", $row['flag']);
            foreach ($arr as $k => $v) {
                $arr[$k] = $v * 1;
            }
            $row['flag'] = $arr;
        } else {
            $row['flag'] = [];
        }
        if ($row['actdata']) {

            $arr = explode(",", $row['actdata']);
            foreach ($arr as $k => $v) {
                $arr[$k] = $v * 1;
            }
            $row['actdata'] = $arr;
        } else {
            $row['actdata'] = [];
        }
        if ($row['city']) {
            $row['city'] = explode(',', $row['city']);
        } else {
            $row['city'] = [];
        }
        if (empty($row)) {
            return error('数据不存在');
        }
        return success('ok', $row);
    }

    /**
     * 导出
     */
    public function export()
    {
        list($limit, $where, $sortArr) = $this->buildTableParames();
        $fields = $this->request->get('fields');
        $fields = json_decode($fields, true);

        $header = [];
        foreach ($fields as $vo) {
            $header[] = [$vo['comment'], $vo['field']];
        }
        $tableName = $this->model->getName();
        $list = $this->model
            ->withJoin('category', 'LEFT')
            ->where($where)
            ->limit(100000)
            ->order($sortArr)
            ->select()
            ->toArray();
        $fileName = "export_" . $tableName . "_" . time();
        return Excel::exportData($list, $header, $fileName, 'xlsx');
    }

    /**
    * 方法详情
    * @param array params
    */
    public function export_customer()
    {
        $post = $this->request->post();
        if(!empty($post['customer_id'])){
            $customer_id = explode(',',$post['customer_id']);
        }else{
            $customer_id = 0;
        }
        $returns = '';
        if(!empty($customer_id)){
            foreach ($customer_id as $k=>$vs) {
                $v = Db::name('customer')->where('id', $vs)->find();
                if(!empty($v)){
                    $iscustom = Db::name('site')->where('id', $post['site_id'])->find();
                    $url = $iscustom['site_url'] . "/admin/api/2023-07/customers.json";
                    $arr = array();
                    $arr['customer']['addresses'][0]['name'] = $v['title'];
                    $arr['customer']['addresses'][0]['first_name'] = $v['firstname'];
                    $arr['customer']['addresses'][0]['last_name'] = $v['lastname'];
                    $arr['customer']['addresses'][0]['company'] = $v['company'];
                    $arr['customer']['addresses'][0]['phone'] = $v['phone'];
                    $arr['customer']['addresses'][0]['address1'] = $v['address1'];
                    $arr['customer']['addresses'][0]['address2'] = $v['address2'];
                    $arr['customer']['addresses'][0]['city'] = $v['city'];
                    $arr['customer']['addresses'][0]['zip'] = $v['zipcode'];
                    $arr['customer']['country'] = $v['country'];
                    $arr['customer']['email'] = $v['email'];
                    $arr['customer']['accepts_marketing'] = true;
                    $arr['customer']['send_email_invite'] = false;
                    $arr['customer']['verified_email'] = false;
                    $arr['customer']['first_name'] = $v['firstname'];
                    $arr['customer']['last_name'] = $v['lastname'];
                    $data2 = posturl($url, $arr, $iscustom);
                    $data = json_decode($data2, true);
                    if (isset($data['errors']) && !empty($data['errors'])) {
                        if (isset($data['errors']['email'][0]) && !empty($data['errors']['email'][0])) {
                            $returns = $returns .'|'.$v['title'].'导入失败(已存在该邮箱)';
                        }else{
                            $returns = $returns .'|'.$v['title'].'导入失败';
                        }
                    }else{
                        $returns = $returns .'|'.$v['title'].'导入成功';
                    }
                }
            }
        }
        return success($returns);
    }
    
}
